﻿using Mysoft.QuickCode.NPOI.SS.UserModel;
using Mysoft.QuickCode.Office.Excel;
using Mysoft.QuickCode.Office.Excel.Data;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;

namespace CSharpProj.test
{
    /// <summary>
    /// 功能测试类
    /// </summary>
    public class ExcelTester
    {
        /// <summary>
        /// 无模板常规导出（带格式）
        /// </summary>
        public static void NoTemplateExport()
        {
            Random rand = new Random(1000000);

            DataTable table = new DataTable();
            table.Columns.Add("姓名", typeof(string));
            table.Columns.Add("年龄", typeof(int));
            table.Columns.Add("资产", typeof(long));
            table.Columns.Add("备注", typeof(string));

            for (int i = 1; i <= 100; i++)
            {
                var row = table.NewRow();
                row["姓名"] = $"王富贵{i}";
                row["年龄"] = $"{rand.Next(20, 60)}";
                row["资产"] = $"{Math.Round(rand.Next(100000000, 999999999) * 100M, 2)}";
                row["备注"] = $"不知道为什么这么富{i}";

                table.Rows.Add(row);
            }

            var dataList = table.ToExcelCellList();

            //可以设置单元格的一些格式
            int nnColIndex = dataList.First().IndexOf(dataList.First().GetExcelCellByField("年龄"));  //获取年龄列的索引，通过GetExcelCellByField来按字段查找单元格。
            int zcColIndex = dataList.First().IndexOf(dataList.First().GetExcelCellByField("资产"));
            for (int i = 1; i < dataList.Count; i++)
            {
                var nn = dataList[i][nnColIndex];
                nn.DataType = "number";
                nn.DataFormat = "0";
                nn.Align = "right";

                var zc = dataList[i][zcColIndex];
                zc.DataType = "number";
                zc.DataFormat = "#,##0.00";
                zc.Align = "right";
                zc.Width = 200;
            }

            var workbook = ExcelCreator.ExportToExcel(null, dataList, null, new ExportExcelSetting()
            {
                Version = OfficeVersion.Office2007Plus
            });

            //保存到文件
            using (FileStream file = new FileStream(@"D:/QC导出测试/常规导出（带格式）.xlsx", FileMode.OpenOrCreate))
            {
                workbook.Write(file);
            }
        }


        /// <summary>
        /// 无模板常规导出（大标题、备注、样式）
        /// </summary>
        public static void NoTemplateExport1()
        {
            Random rand = new Random(1000000);

            DataTable table = new DataTable();
            table.Columns.Add("姓名", typeof(string));
            table.Columns.Add("年龄", typeof(int));
            table.Columns.Add("资产", typeof(long));
            table.Columns.Add("备注", typeof(string));

            for (int i = 1; i <= 100; i++)
            {
                var row = table.NewRow();
                row["姓名"] = $"王富贵{i}";
                row["年龄"] = $"{rand.Next(20, 60)}";
                row["资产"] = $"{Math.Round(rand.Next(100000000, 999999999) * 100M, 2)}";
                row["备注"] = $"不知道为什么这么富{i}";

                table.Rows.Add(row);
            }

            var dataList = table.ToExcelCellList();
            var maxColumn = dataList.First().Count;

            //大标题、备注、样式
            var title = new List<ExcelCell>();
            var memo = new List<ExcelCell>();
            for (int i = 0; i < maxColumn; i++)
            {
                title.Add(new ExcelCell()
                {
                    IsTitle = true,   //标记为标题
                    Height = 50,
                    Text = "大标题"
                });

                memo.Add(new ExcelCell()
                {
                    IsSummary = true,   //标记为摘要备注行
                    Height = 50,
                    FontColor = Mysoft.QuickCode.NPOI.HSSF.Util.HSSFColor.Red.Index,
                    Text = @"
1.说明文字1
2.说明文字2
"
                });
            }

            //插入到导数列表
            dataList.Insert(0, memo);
            dataList.Insert(0, title);

            var workbook = ExcelCreator.ExportToExcel(null, dataList, null, new ExportExcelSetting()
            {
                Version = OfficeVersion.Office2007Plus,
                HeaderRowCount = 3    //因为加入了大标题、备注行，所以行头一共有3行了。
            });

            //保存到文件
            using (FileStream file = new FileStream(@"D:/QC导出测试/常规导出（大标题、备注、样式）.xlsx", FileMode.OpenOrCreate))
            {
                workbook.Write(file);
            }
        }


        /// <summary>
        /// 无模板常规导出（多级表头）
        /// </summary>
        public static void NoTemplateExport2()
        {
            Random rand = new Random(1000000);

            DataTable table = new DataTable();
            table.Columns.Add("姓名", typeof(string));
            table.Columns.Add("年龄", typeof(int));
            table.Columns.Add("资产", typeof(long));
            table.Columns.Add("备注", typeof(string));

            for (int i = 1; i <= 100; i++)
            {
                var row = table.NewRow();
                row["姓名"] = $"王富贵{i}";
                row["年龄"] = $"{rand.Next(20, 60)}";
                row["资产"] = $"{Math.Round(rand.Next(100000000, 999999999) * 100M, 2)}";
                row["备注"] = $"不知道为什么这么富{i}";

                table.Rows.Add(row);
            }

            var dataList = table.ToExcelCellList();

            //多级表头
            var firstRow = new List<ExcelCell>();
            firstRow.Add(new ExcelCell()
            {
                Text = "个人信息"
            });
            firstRow.Add(new ExcelCell()
            {
                Text = "个人信息"
            });
            firstRow.Add(new ExcelCell()
            {
                Text = "资产"
            });
            firstRow.Add(new ExcelCell()
            {
                Text = "备注"
            });

            //插入合并头行到表头
            dataList.Insert(0, firstRow);

            var workbook = ExcelCreator.ExportToExcel(null, dataList, null, new ExportExcelSetting()
            {
                Version = OfficeVersion.Office2007Plus,
                HeaderRowCount = 2    //因为加入了合并行，所以标题为2行。
            });

            //保存到文件
            using (FileStream file = new FileStream(@"D:/QC导出测试/常规导出（多级表头）.xlsx", FileMode.OpenOrCreate))
            {
                workbook.Write(file);
            }
        }

        /// <summary>
        /// 无模板常规导出（追加多页签）
        /// </summary>
        public static void NoTemplateExport3()
        {
            IWorkbook workbook = null;

            for (int p = 0; p < 5; p++)
            {
                Random rand = new Random(1000000);

                DataTable table = new DataTable();
                table.Columns.Add("姓名", typeof(string));
                table.Columns.Add("年龄", typeof(int));
                table.Columns.Add("资产", typeof(long));
                table.Columns.Add("备注", typeof(string));

                for (int i = 1; i <= 100; i++)
                {
                    var row = table.NewRow();
                    row["姓名"] = $"王富贵{i}";
                    row["年龄"] = $"{rand.Next(20, 60)}";
                    row["资产"] = $"{Math.Round(rand.Next(100000000, 999999999) * 100M, 2)}";
                    row["备注"] = $"不知道为什么这么富{i}";

                    table.Rows.Add(row);
                }

                var dataList = table.ToExcelCellList();

                workbook = ExcelCreator.ExportToExcel(workbook, dataList, null, new ExportExcelSetting()
                {
                    Version = OfficeVersion.Office2007Plus
                },
                sheetName: $"页签{p}",
                createMode: CreateMode.AppendSheet);
            }

            //保存到文件
            if (File.Exists(@"D:/QC导出测试/常规导出（追加多页签）.xlsx"))
            {
                File.Delete(@"D:/QC导出测试/常规导出（追加多页签）.xlsx");
            }
            using (FileStream file = new FileStream(@"D:/QC导出测试/常规导出（追加多页签）.xlsx", FileMode.OpenOrCreate))
            {
                workbook.Write(file);
            }
        }


        /// <summary>
        /// 无模板常规导出（同页签多个表格XLS格式）
        /// </summary>
        public static void NoTemplateExport4()
        {
            var tabKvList = new List<KeyValuePair<string, DataTable>>();

            for (int p = 0; p < 5; p++)
            {
                Random rand = new Random(1000000);

                DataTable table = new DataTable();
                table.Columns.Add("姓名", typeof(string));
                table.Columns.Add("年龄", typeof(int));
                table.Columns.Add("资产", typeof(long));
                table.Columns.Add("备注", typeof(string));

                for (int i = 1; i <= 3; i++)
                {
                    var row = table.NewRow();
                    row["姓名"] = $"王富贵{i}";
                    row["年龄"] = $"{rand.Next(20, 60)}";
                    row["资产"] = $"{Math.Round(rand.Next(100000000, 999999999) * 100M, 2)}";
                    row["备注"] = $"不知道为什么这么富{i}";

                    table.Rows.Add(row);
                }

                tabKvList.Add(new KeyValuePair<string, DataTable>($"表格{p}", table));
            }

            var dst = new DataSetTool(tabKvList);
            IWorkbook workbook = dst.GetWorkbook();

            //保存到文件
            using (FileStream file = new FileStream(@"D:/QC导出测试/常规导出（同页签多个表格XLS格式）.xls", FileMode.OpenOrCreate))
            {
                workbook.Write(file);
            }
        }



        /// <summary>
        /// 无模板高级导出（下拉框）
        /// </summary>
        public static void NoTemplateExportEx1()
        {
            new DPDExportTest().Export();
        }

        /// <summary>
        /// 无模板高级导出（规则计算公式）
        /// </summary>
        public static void NoTemplateExportEx2()
        {
            new FormulaExportTest().Export();
        }

        /// <summary>
        /// 无模板高级导出（不规则计算公式）
        /// </summary>
        public static void NoTemplateExportEx3()
        {
            new FormulaExportTest().Export2();
        }
    }
}
